Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

Index cursors

The OpenEdge Index Cursor (-c) connection parameter sets the maximum number of ORACLE cursors that the DataServer client session uses when you connect to an ORACLE database. Specify -c after you specify the name of the ORACLE database (-db database-name) in the list of parameters.

The DataServer uses cursors whenever it executes an SQL statement to access data in a table. Each ORACLE cursor uses up to 4K of memory. To minimize memory consumption, the DataServer attempts to free and reuse ORACLE cursors as soon as possible. It also reuses cursors that are active (not free) if there are no free cursors available. This might reduce performance, but it allows the application to continue even if there are not enough cursors. The Progress 4GL uses a least-recently-used algorithm to select which active cursor to reuse.

ORACLE allows you to set the maximum number of cursors in your init.ora file using the OPEN_CURSORS parameter. The valid range for numbers of cursors varies depending on the version of ORACLE and system configuration.

The Progress 4GL default maximum number of ORACLE open cursors for the DataServer is 50 also. When you use the -c parameter to set the maximum number of cursors, you cannot exceed the number that your init.ora file specifies. For example, if the ORACLE OPEN_CURSORS parameter is set to 250, then you can set the upper limit for maximum open cursors open to 250 with the -c parameter.

Determining the optimal number of cursors for your application involves balancing memory consumption, performance, and possible application failures. Use the -Dsrv qt_debug,EXTENDED parameter to log information on how many cursors your application uses. The following excerpt from the dataserv.lg file shows the cursor handler identifier within the angle brackets (<n>) that the DataServer uses for each OCI call:

OCI call OCIStmtPrepare <2> sqlcrc = 41633 SELECT /*+ INDEX(T0 CUSTOMER##PROGRESS_RECID) */ * FROM DOCTEST.CUSTOMER T0 WHERE PROGRESS_RECID = :rid OCI call OCIStmtExecute-DescribeOnly <2> OCI call omru <2> OCI call OCIHandleAlloc <0> OCI call OCIStmtPrepare <3> sqlcrc = 60664 SELECT /*+ INDEX(T0 ORDER_##PROGRESS_RECID) */ * FROM DOCTEST.ORDER_ T0 WHERE PROGRESS_RECID = :rid OCI call OCIStmtExecute-DescribeOnly <3> OCI call omru <3> OCI call OCIHandleAlloc <0> OCI call OCIStmtPrepare <4> sqlcrc = 56980 SELECT /*+ INDEX_ASC(T0 CUSTOMER##COUNTRY_POST) */ PROGRESS_RECID unique_id_0,CUST_NUM,COUNTRY,NAME,ADDRESS,ADDRESS2,CITY,STATE, POSTAL_CODE,CONTACT,PHONE,SALES_REP,CREDIT_LIMIT,BALANCE,TERMS, DISCOUNT,COMMENTS,PROGRESS_RECID FROM DOCTEST.CUSTOMER T0 OCI call OCIStmtExecute <4> OCI call OCIHandleAlloc <0> OCI call OCIStmtPrepare <5> sqlcrc = 48586 SELECT /*+ INDEX_ASC(T0 ORDER_##CUST_ORDER) */ PROGRESS_RECID un ique_id_0,ORDER_NUM,CUST_NUM,ORDER_DATE,SHIP_DATE,PROMISE_DATE,CA RRIER,INSTRUCTIONS,PO,TERMS,SALES_REP,PROGRESS_RECID FROM DOCTEST .ORDER_ T0 WHERE (CUST_NUM = :1) OCI call OCIStmtExecute <5>

Avoid setting the -c parameter too low or too high:

Too low — A low setting can cause unnecessary recompiles of SQL, which hurts performance. Your application could also fail because it opens more queries, nested FOR EACH, or FIND statements, that reference different indexes, than the -c parameter allows.
Too high — A high setting can cause unnecessary consumption of resources such as memory and cursors, which can hurt performance when they are not reused. Your application can also fail when you allocate all available cursors, including a cursor that the ORACLE DBMS needs for internal purposes. If this occurs, ORACLE returns a recursive SQL error.